from app.extensions import db
from app.persistence.Address import Address
from app.persistence.Facilities import Facility
from app.persistence.HouseImage import HouseImage
from app.persistence.HouseInfo import HouseInfo
from app.persistence.HouseReviews import HouseReviews
from sqlalchemy import cast, Float  # 导入cast和Float

class HouseService:
    #-------房源基础操作------------
    @staticmethod
    def create_house(house_data,address_data,facility_data):
        """创建房源（包含基础信息，地址和设施)"""
        try:
            #创建基础信息
            house=HouseInfo(
                title=house_data['title'],
                property_type=house_data['property_type'],
                rental_type=house_data['rental_type'],
                area=house_data['area'],
                price=house_data['price'],
                deposit=house_data.get('deposit',0),
                bedroom_count=house_data.get('bedroom_count',0),
                livingroom_count=house_data.get('livingroom_count',0),
                bathroom_count=house_data.get('bathroom_count',0),
                status='pending',
                userid=house_data['userid'],
            )
            db.session.add(house)
            db.session.flush()  #获取houseid

            #创建地址信息
            address=Address(
                houseid=house.houseid,
                province=address_data['province'],
                city=address_data['city'],
                district=address_data['district'],
                street=address_data['street'],
                location=address_data['location'],
            )
            db.session.add(address)

            # 创建设施信息
            facility = Facility(
                houseid=house.houseid,
                has_lift=facility_data.get('has_lift', None),
                has_parking=facility_data.get('has_parking', None),
                has_wifi=facility_data.get('has_wifi', None),
                has_kitchen=facility_data.get('has_kitchen', None),
                has_washer=facility_data.get('has_washer', None),
                has_ac=facility_data.get('has_ac', None),
                has_heating=facility_data.get('has_heating', None),
                has_tv=facility_data.get('has_tv', None),
            )
            db.session.add(facility)

            db.session.commit()
            return house
        except Exception as e:
            db.session.rollback()
            raise e


    @staticmethod
    def delete_house(house_id):
        """删除房源（级联删除相关记录）"""
        try:
            #删除评论（如果需要保留评论，可以跳过）
            HouseReviews.query.filter_by(houseid=house_id).delete()

            #删除设施和地址
            Facility.query.filter_by(houseid=house_id).delete()
            Address.query.filter_by(houseid=house_id).delete()

            #删除图片
            HouseImage.query.filter_by(houseid=house_id).delete()

            #删除房源基础信息
            rows_deleted=HouseInfo.query.filter_by(houseid=house_id).delete()

            db.session.commit()
            return rows_deleted>0
        except Exception as e:
            db.session.rollback()
            raise e


    @staticmethod
    def update_house(houseid,update_data):
        """更新房源信息"""
        try:
            house=HouseInfo.query.get(houseid)
            if not house:
                return False

            for key,value in update_data.items():
                if hasattr(house,key):
                    setattr(house,key,value)

            db.session.commit()
            return True
        except Exception as e:
            db.session.rollback()
            raise e



    @staticmethod
    def update_address(houseid,address_data):
        """更新房源地址"""
        try:
            address=Address.query.get(houseid)
            if not address:
                return False

            for key,value in address_data.items():
                if hasattr(address,key):
                    setattr(address,key,value)


            db.session.commit()
            return True

        except Exception as e:
            db.session.rollback()
            raise e




    @staticmethod
    def update_facility(houseid,facility_data):
        """更新房源设施"""
        try:
            facility=Facility.query.get(houseid)
            if not facility:
                return False

            for key,value in facility_data.items():
                if hasattr(facility,key):
                    setattr(facility,key,value)

            db.session.commit()
            return True
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def get_house_info(houseid, page=1, per_page=5):
        """
        获取房源完整信息（包含基础信息、地址、设施、评价统计和具体评价、图片）

        参数:
            houseid: 房源ID
            page: 评价分页页码（默认第1页）
            per_page: 每页评价数量（默认5条）
        """
        try:
            house = HouseInfo.query.get(houseid)

            if not house:
                return None

            address = Address.query.get(houseid)
            facility = Facility.query.get(houseid)

            # 获取评价统计
            review_status = HouseService._calculate_review_stats(houseid)

            # 获取具体评价（分页）
            reviews_query = HouseReviews.query.filter_by(houseid=houseid)
            reviews_pagination = reviews_query.paginate(page=page, per_page=per_page)

            # 组装评价列表
            reviews = []
            for review in reviews_pagination.items:
                reviews.append({
                    'review_id': review.reviewid,
                    'user_id': review.userid,
                    'comment': review.comment,
                    'ratings': {
                        'cleanliness': review.cleanliness_rating,
                        'location': review.location_rating,
                        'facility': review.facility_rating,
                        'value': review.value_rating,
                        'communication': review.communication_rating
                    },
                    'overall_rating': (review.cleanliness_rating +
                                       review.location_rating +
                                       review.facility_rating +
                                       review.value_rating +
                                       review.communication_rating) / 5.0,
                    'review_time': review.review_time.isoformat() if review.review_time else None
                })

            # 获取房源图片
            images = {'cover': None, 'gallery': []}

            # 封面图
            cover_image = HouseImage.query.filter_by(
                houseid=houseid,
                is_cover=True
            ).first()

            if cover_image:
                images['cover'] = {
                    'imageid': cover_image.imageid,
                    'image_url': cover_image.image_url
                }

            # 其他图片（按上传时间倒序）
            other_images = HouseImage.query.filter_by(
                houseid=houseid,
                is_cover=False
            ).order_by(
                HouseImage.upload_time.desc()
            ).all()

            images['gallery'] = [
                {
                    'imageid': img.imageid,
                    'image_url': img.image_url,
                    'is_cover': img.is_cover,
                    'upload_time': img.upload_time.isoformat() if img.upload_time else None
                }
                for img in other_images
            ]

            # 构建返回结果
            return {
                'house': house.to_dict(),
                'address': address.to_dict() if address else None,
                'facility': {
                    'has_lift': facility.has_lift if facility else None,
                    'has_parking': facility.has_parking if facility else None,
                    'has_wifi': facility.has_wifi if facility else None,
                    'has_kitchen': facility.has_kitchen if facility else None,
                    'has_washer': facility.has_washer if facility else None,
                    'has_ac': facility.has_ac if facility else None,
                    'has_heating': facility.has_heating if facility else None,
                    'has_tv': facility.has_tv if facility else None,
                },
                'images': images,
                'review_status': review_status,
                'reviews': {
                    'items': reviews,
                    'total': reviews_pagination.total,
                    'pages': reviews_pagination.pages,
                    'current_page': page
                }
            }
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def search_houses(filters=None, page=1, per_page=10):
        """综合搜索房源（返回完整信息）"""
        try:
            # 基础查询（默认只查已审核房源）
            query = HouseInfo.query.filter_by(status='pending')

            if filters:
                # 基础信息筛选
                if 'title' in filters:
                    query = query.filter(HouseInfo.title.like(f"%{filters['title']}%"))
                if 'property_type' in filters:
                    query = query.filter_by(property_type=filters['property_type'])
                if 'rental_type' in filters:
                    query = query.filter_by(rental_type=filters['rental_type'])
                if 'min_price' in filters:
                    query = query.filter(HouseInfo.price >= filters['min_price'])
                if 'max_price' in filters:
                    query = query.filter(HouseInfo.price <= filters['max_price'])
                if 'status' in filters:  # 允许覆盖默认状态筛选
                    query = query.filter_by(status=filters['status'])

                # 地址筛选（联表查询）
                if any(key in filters for key in ['province', 'city', 'district']):
                    query = query.join(Address)
                    if 'province' in filters:
                        query = query.filter(Address.province == filters['province'])
                    if 'city' in filters:
                        query = query.filter(Address.city == filters['city'])
                    if 'district' in filters:
                        query = query.filter(Address.district == filters['district'])

                # 设施筛选（联表查询）
                facility_filters = {
                    k: v for k, v in filters.items()
                    if k.startswith('has_') and v is not None
                }
                if facility_filters:
                    query = query.join(Facility)
                    for attr, value in facility_filters.items():
                        query = query.filter(getattr(Facility, attr) == value)

            # 分页处理
            pagination = query.paginate(page=page, per_page=per_page)

            # 组装完整结果
            houses = []
            for house in pagination.items:
                house_data = house.to_dict()
                house_data['address'] = Address.query.get(house.houseid).to_dict()
                house_data['facility'] = Facility.query.get(house.houseid).to_dict()
                house_data['review_status'] = HouseService._calculate_review_stats(house.houseid)
                # houses.append(house_data)
                # ---------- 新增：获取房源图片 ----------
                # 封面图
                cover_image = HouseImage.query.filter_by(
                    houseid=house.houseid,
                    is_cover=True
                ).first()

                # 其他图片（按上传时间倒序）
                other_images = HouseImage.query.filter_by(
                    houseid=house.houseid
                ).order_by(
                    HouseImage.upload_time.desc()
                ).all()

                # 组装图片数据
                house_data['images'] = {
                    'cover': {
                        'imageid': cover_image.imageid if cover_image else None,
                        'image_url': cover_image.image_url if cover_image else None
                    },
                    'gallery': [
                        {
                            'imageid': img.imageid,
                            'image_url': img.image_url,
                            'is_cover': img.is_cover,
                            'upload_time': img.upload_time.isoformat() if img.upload_time else None
                        }
                        for img in other_images
                    ]
                }
                houses.append(house_data)
            return {
                'houses': houses,
                'total': pagination.total,
                'pages': pagination.pages,
                'current_page': page
            }
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def get_houses_by_user(userid):
        """获取用户房源并按状态分类（包含完整信息：基础信息+地址+设施+评价统计）"""
        try:
            # 查询该用户所有房源
            houses = HouseInfo.query.filter_by(userid=userid).all()

            # 按状态分类初始化
            result = {
                'pending': [],
                'published': [],
                'rejected': [],
                'rented': [],
                'hidden': []
            }

            for house in houses:
                # 组装基础信息
                house_data = house.to_dict()

                # 添加地址信息
                address = Address.query.get(house.houseid)
                house_data['address'] = address.to_dict() if address else None

                # 添加设施信息
                facility = Facility.query.get(house.houseid)
                house_data['facility'] = {
                    'has_lift': facility.has_lift if facility and facility.has_lift else None,
                    'has_parking': facility.has_parking if facility and facility.has_parking else None,
                    'has_wifi': facility.has_wifi if facility and facility.has_wifi else None,
                    'has_kitchen': facility.has_kitchen if facility and facility.has_kitchen else None,
                    'has_washer': facility.has_washer if facility and facility.has_washer else None,
                    'has_ac': facility.has_ac if facility and facility.has_ac else None,
                    'has_heating': facility.has_heating if facility and facility.has_heating else None,
                    'has_tv': facility.has_tv if facility and facility.has_tv else None,
                }

                # 添加评价统计
                house_data['review_status'] = HouseService._calculate_review_stats(house.houseid)

                # ---------- 新增：获取房源图片 ----------
                # 封面图
                cover_image = HouseImage.query.filter_by(
                    houseid=house.houseid,
                    is_cover=True
                ).first()

                # 其他图片（按上传时间倒序）
                other_images = HouseImage.query.filter_by(
                    houseid=house.houseid
                ).order_by(
                    HouseImage.upload_time.desc()
                ).all()

                # 组装图片数据
                house_data['images'] = {
                    'cover': {
                        'imageid': cover_image.imageid if cover_image else None,
                        'image_url': cover_image.image_url if cover_image else None
                    },
                    'gallery': [
                        {
                            'imageid': img.imageid,
                            'image_url': img.image_url,
                            'is_cover': img.is_cover,
                            'upload_time': img.upload_time.isoformat() if img.upload_time else None
                        }
                        for img in other_images
                    ]
                }
                # 按状态分类（不存在的状态归为pending）
                status = house.status.lower() if house.status else 'pending'
                if status in result:
                    result[status].append(house_data)
                else:
                    result['pending'].append(house_data)

            return result
        except Exception as e:
            db.session.rollback()
            raise e


    @staticmethod
    def get_all_houses(page=1, per_page=10):
        """获取所有房源列表（分页，包含完整信息）"""
        try:
            # 基础查询（只查询已审核通过的房源）
            query = HouseInfo.query.filter_by(status='published')

            # 分页处理
            pagination = query.paginate(page=page, per_page=per_page)

            # 结果组装
            houses = []
            for house in pagination.items:
                # 基础信息
                house_data = house.to_dict()

                # 地址信息
                address = Address.query.get(house.houseid)
                house_data['address'] = address.to_dict() if address else None

                # 设施信息
                facility = Facility.query.get(house.houseid)
                house_data['facility'] = {
                    'has_lift': facility.has_lift if facility and facility.has_lift else None,
                    'has_parking': facility.has_parking if facility and facility.has_parking else None,
                    'has_wifi': facility.has_wifi if facility and facility.has_wifi else None,
                    'has_kitchen': facility.has_kitchen if facility and facility.has_kitchen else None,
                    'has_washer': facility.has_washer if facility and facility.has_washer else None,
                    'has_ac': facility.has_ac if facility and facility.has_ac else None,
                    'has_heating': facility.has_heating if facility and facility.has_heating else None,
                    'has_tv': facility.has_tv if facility and facility.has_tv else None,
                }

                # 评价统计
                house_data['review_status'] = HouseService._calculate_review_stats(house.houseid)

                # ---------- 新增：获取房源图片 ----------
                # 封面图
                cover_image = HouseImage.query.filter_by(
                    houseid=house.houseid,
                    is_cover=True
                ).first()

                # 其他图片（按上传时间倒序）
                other_images = HouseImage.query.filter_by(
                    houseid=house.houseid
                ).order_by(
                    HouseImage.upload_time.desc()
                ).all()

                # 组装图片数据
                house_data['images'] = {
                    'cover': {
                        'imageid': cover_image.imageid if cover_image else None,
                        'image_url': cover_image.image_url if cover_image else None
                    },
                    'gallery': [
                        {
                            'imageid': img.imageid,
                            'image_url': img.image_url,
                            'is_cover': img.is_cover,
                            'upload_time': img.upload_time.isoformat() if img.upload_time else None
                        }
                        for img in other_images
                    ]
                }
                houses.append(house_data)

            return {
                'houses': houses,
                'total': pagination.total,
                'pages': pagination.pages,
                'current_page': page
            }
        except Exception as e:
            db.session.rollback()
            raise e

    @staticmethod
    def _calculate_review_stats(houseid):
        """计算房源评价统计（修复版）"""
        review_count = HouseReviews.query.filter_by(houseid=houseid).count()

        # 关键修改：将每个评分字段转换为Float，避免整数截断
        avg_rating = db.session.query(
            db.func.avg(
                (cast(HouseReviews.cleanliness_rating, Float) +
                 cast(HouseReviews.location_rating, Float) +
                 cast(HouseReviews.facility_rating, Float) +
                 cast(HouseReviews.value_rating, Float) +
                 cast(HouseReviews.communication_rating, Float)) / 5.0
            )
        ).filter_by(houseid=houseid).scalar()

        return {
            'count': review_count,
            'average_rating': round(avg_rating, 1) if avg_rating else None
        }

    @staticmethod
    def is_house_owner(houseid, userid):
        """验证用户是否为房源所有者"""
        userid=int(userid)
        house = HouseInfo.query.get(houseid)
        return house and house.userid == userid
